package dbManagement;

import com.mysql.jdbc.ResultSet;
import core.PatientBean;
import hirondelle.date4j.DateTime;
import java.io.FileNotFoundException;
import java.io.PrintStream;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import logManagement.Log4k;
import objects.EGender;
import objects.userKind;
import objects.visitKind;

public class DBManager_tester {
    private static final boolean test = true;
    
    /* set fileout to the name of the file or leave it empty to use the console */
    private static final String fileout = "";
    
    private static final String patientUsername = "arcuri_1";
    private static final String doctorUsername = "arcuri_3";
    private static final String uslUsername = "rizzo_1";
    private static final String noOneUsername = "I_am_nobody";
    
    private static final String patientPwd = "default";
    private static final String doctorPwd = "arcuri";
    private static final String uslPwd = "rizzo";
    private static final String wrongPwd = "WRONG";
    
    private static final int patient_id = 1;
    private static final int doctor_id = 3;
    private static final int notMyDoctorButUsl_id = 2;
    private static final int notMyDoctor_id = 1;
    private static final int usl_id = 2;
    private static final int record_id = 1;
    private static final int chiefTain_id = 1;
    private static final int notAChiefTain_id = 2;
    private static final int false_chiefTain_id = -1;
    private static final int false_record_id = -1;
    private static final int false_patient_id = -1;
    private static final int false_doctor_id = -1;
    private static final int false_usl_id = -1;

    private static final String patientCell = "03702629004";
    private static final String doctorCell = "+393204004460";
    private static final String uslCell = "+393333781087";
    private static final String nobodysCell = "not_a_cell_number";
        
    private static final String report = "Here's the report";
    private static final int program_id = 1;
    private static final DateTime date = new DateTime("2010-10-10 00:00:00");
    
    private static final String DATE_FORMAT = "YYYY-MM-DD hh:mm:ss";
    
    private static void printSqlSet(ResultSet sqlSet, PrintStream out){
        if (sqlSet == null) return;
        try {
            String separator = "";
            for (int i=0; i<200; i++) separator += "-";
            
            out.println(separator);
            
            ResultSetMetaData metaData = sqlSet.getMetaData();
            String row_names = "";
            for (int i=1; i<=metaData.getColumnCount(); i++){
                row_names += metaData.getColumnName(i) + "\t";
            }
            out.println(row_names);
            
            if (sqlSet.first()){                
                while (!sqlSet.isAfterLast()){
                    String tuple = "";
                    for (int i=1; i<=metaData.getColumnCount(); i++)
                        tuple += sqlSet.getString(i) + "\t";
                    out.println(tuple);
                    sqlSet.next();
                }
            }
            
            out.println(separator);
        } catch (SQLException ex) {
            Log4k.error(DBManager_tester.class.getName(), ex.getMessage());
        }
    }
    
    public static void main(String[] args) {
        PrintStream out;
        if (!fileout.equals("")){
            try {
                out = new PrintStream(fileout, "UTF-8");
            } catch (FileNotFoundException ex) {
                out = System.out;
            } catch (UnsupportedEncodingException ex) {
                out = System.out;
            }
        } else {
            out = System.out;
        }
        dbManager db = new dbManager();
        
        if (!test){            
            try {   
                final int TESTS = 10;
                Integer[] results = new Integer[TESTS];
                
                results[0] = db.addVisit(patient_id, doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                results[1] = db.addVisit(patient_id, doctor_id, program_id, date, visitKind.VACCINATION, userKind.DOCTOR);
                results[2] = db.addVisit(patient_id, doctor_id, program_id, date, visitKind.PATIENT_REQUEST, userKind.DOCTOR);
                
                /* the followings should return an error */
                results[3] = db.addVisit(patient_id, doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.PATIENT);                
                results[4] = db.addVisit(patient_id, doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.USL);
                
                results[5] = db.addVisit(patient_id, false_doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                results[6] = db.addVisit(false_patient_id, false_doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                results[7] = db.addVisit(false_patient_id, doctor_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                
                results[8] = db.addVisit(patient_id, notMyDoctor_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                results[9] = db.addVisit(patient_id, notMyDoctorButUsl_id, program_id, date, visitKind.PROGRAMMED, userKind.DOCTOR);
                
                out.println("RESULTS:\t");
                for (int i=0; i<TESTS; i++){
                    out.println(results[i] + " ");
                }
            } catch (notMyPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }
        }
        
        if (!test){
            db.changeEmail(patient_id, userKind.PATIENT, "patient_email@attempt.mcr");
            db.changeEmail(doctor_id, userKind.DOCTOR, "doctor_email@attempt.mcr");
            db.changeEmail(usl_id, userKind.USL, "usl_email@attempt.mcr");
            
            /* tloggerhe followings should return an error */
            db.changeEmail(false_patient_id, userKind.PATIENT, "patient_email@attempt.mcr");
            db.changeEmail(false_doctor_id, userKind.DOCTOR, "doctor_email@attempt.mcr");
            db.changeEmail(false_usl_id, userKind.USL, "usl_email@attempt.mcr");
            db.changeEmail(usl_id, userKind.DOCTOR, "usl_email@attempt.mcr");
            db.changeEmail(doctor_id, userKind.USL, "patient_email@attempt.mcr");
        }
        
        if (!test){
            db.changePassword(patientUsername, userKind.PATIENT, "patient_pwd");
            db.changePassword(doctorUsername, userKind.DOCTOR, "doctor_pwd");
            db.changePassword(uslUsername, userKind.USL, "usl_pwd");
            
            /* the followings should return an error */
            db.changePassword(noOneUsername, userKind.PATIENT, "patient_pwd");
            db.changePassword(noOneUsername, userKind.DOCTOR, "doctor_pwd");
            db.changePassword(noOneUsername, userKind.USL, "usl_pwd");
            db.changePassword(uslUsername, userKind.DOCTOR, "usl_pwd");
            db.changePassword(doctorUsername, userKind.USL, "patient_pwd");
        }
        
        if (!test){
            DateTime dateToComparePassed = db.now().minusDays(1);
            DateTime dateToCompareInsideInterval = db.now();
            DateTime dateToCompareComing = db.now().plusDays(1);
            int interval = 60*60; // 1 hour
            
            int a, b, c;
            a = db.compareForUrgency(dateToComparePassed, interval);
            b = db.compareForUrgency(dateToCompareInsideInterval, interval);
            c = db.compareForUrgency(dateToCompareComing, interval);
            
            out.println(a + ", " + b + ", " + c);
        }

        if (!test){
            try {
                db.doExamine(doctor_id, patient_id, record_id, report, userKind.DOCTOR);
                
                /* the followings should return an error */
                db.doExamine(doctor_id, patient_id, record_id, report, userKind.PATIENT);
                db.doExamine(doctor_id, patient_id, record_id, report, userKind.USL);
                
                db.doExamine(doctor_id, patient_id, false_record_id, report, userKind.DOCTOR);
                
                db.doExamine(false_doctor_id, patient_id, record_id, report, userKind.DOCTOR);
                db.doExamine(notMyDoctorButUsl_id, patient_id, record_id, report, userKind.DOCTOR);
                db.doExamine(notMyDoctor_id, patient_id, record_id, report, userKind.DOCTOR);
                
                db.doExamine(doctor_id, false_patient_id, record_id, report, userKind.DOCTOR);
                
            } catch (notMyPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }
        }
        
        if (!test){
            out.println(db.existsThePatient(patient_id));
            
            /* the followings should return an error */
            out.println(db.existsThePatient(false_patient_id));
        }
        
        if (!test){
            ResultSet sqlSet = db.getAllDoctors();
            printSqlSet(sqlSet, out);
        }
        
        if (!test){
            ResultSet sqlSet = db.getAllPatients();
            printSqlSet(sqlSet, out);
        }
        
        if (!test){
            int a, b, c;
            
            a = db.getChiefTainByID(chiefTain_id);
            
            /* the followings should return an error */
            b = db.getChiefTainByID(notAChiefTain_id);
            c = db.getChiefTainByID(false_chiefTain_id);
            
            out.println(a + ", " + b + ", " + c);
        }
        
        if (!test){
            int TESTS = 8;
            String[] results = new String[TESTS];
            
            results[0] = db.getEmailByID(patient_id, userKind.PATIENT);
            results[1] = db.getEmailByID(doctor_id, userKind.DOCTOR);
            results[2] = db.getEmailByID(usl_id, userKind.USL);                    
            
            /* the followings should return an error */
            results[3] = db.getEmailByID(doctor_id, userKind.USL);
            results[4] = db.getEmailByID(usl_id, userKind.DOCTOR);
            
            results[5] = db.getEmailByID(false_patient_id, userKind.PATIENT);
            results[6] = db.getEmailByID(false_doctor_id, userKind.DOCTOR);
            results[7] = db.getEmailByID(false_usl_id, userKind.USL);
                        
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
            }
        }
        
        if (!test){  
            int TESTS = 8;
            String[] results = new String[TESTS];
            
            results[0] = db.getEmailByUsername(patientUsername, userKind.PATIENT);
            results[1] = db.getEmailByUsername(doctorUsername, userKind.DOCTOR);
            results[2] = db.getEmailByUsername(uslUsername, userKind.USL);                    
            
            /* the followings should return an error */
            results[3] = db.getEmailByUsername(doctorUsername, userKind.USL);
            results[4] = db.getEmailByUsername(uslUsername, userKind.DOCTOR);
            
            results[5] = db.getEmailByUsername(noOneUsername, userKind.PATIENT);
            results[6] = db.getEmailByUsername(noOneUsername, userKind.DOCTOR);
            results[7] = db.getEmailByUsername(noOneUsername, userKind.USL);
                      
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
            }
        }
        
        if (!test){
            int TESTS = 8;
            Integer[] results = new Integer[TESTS];
            
            results[0] = db.getIDByCell(patientCell, userKind.PATIENT);
            results[1] = db.getIDByCell(doctorCell, userKind.DOCTOR);
            results[2] = db.getIDByCell(uslCell, userKind.USL);
            
            /* the followings should return an error */
            results[3] = db.getIDByCell(doctorCell, userKind.USL);
            results[4] = db.getIDByCell(uslCell, userKind.DOCTOR);
            
            results[5] = db.getIDByCell(nobodysCell, userKind.PATIENT);
            results[6] = db.getIDByCell(nobodysCell, userKind.DOCTOR);
            results[7] = db.getIDByCell(nobodysCell, userKind.USL);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
            }
        }
        
        if (!test){
            /* Q: wouldn't it be better to add a patient for a control? */            
            int TESTS = 4;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getMyDoctorNameByCell(doctorCell);
                 
            /* the followings should return an error */
            results[1] = db.getMyDoctorNameByCell(patientCell);
            results[2] = db.getMyDoctorNameByCell(uslCell);
            results[3] = db.getMyDoctorNameByCell(nobodysCell);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 5;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getMyScheduledVisits(doctor_id, userKind.DOCTOR);
            
            /* the followings should return an error */
            results[1] = db.getMyScheduledVisits(doctor_id, userKind.PATIENT);
            results[2] = db.getMyScheduledVisits(doctor_id, userKind.USL);
            
            results[3] = db.getMyScheduledVisits(usl_id, userKind.DOCTOR);
            results[4] = db.getMyScheduledVisits(false_doctor_id, userKind.DOCTOR);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 4;
            ResultSet[] results = new ResultSet[TESTS];
            
            int LIMIT = 5;
            results[0] = db.getNextNVisits(patient_id, userKind.PATIENT, LIMIT);
            
            /* the followings should return an error */
            results[1] = db.getNextNVisits(patient_id, userKind.DOCTOR, LIMIT);
            results[2] = db.getNextNVisits(patient_id, userKind.USL, LIMIT);
            
            results[3] = db.getNextNVisits(false_patient_id, userKind.PATIENT, LIMIT);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getNextTwoYetUnscheduledVisits(patientCell);
            
            /* the followings should return an error */
            results[1] = db.getNextTwoYetUnscheduledVisits(nobodysCell);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 12;
            ResultSet[] results = new ResultSet[TESTS];
            
            try {
                results[0] = db.getNextVisits(patient_id, userKind.PATIENT);
                results[1] = db.getNextVisits(patient_id, doctor_id, userKind.DOCTOR);
                
                /* the followings should return an error */
                results[2] = db.getNextVisits(patient_id, userKind.DOCTOR);
                results[3] = db.getNextVisits(patient_id, userKind.USL);
                results[4] = db.getNextVisits(patient_id, doctor_id, userKind.PATIENT);
                results[5] = db.getNextVisits(patient_id, doctor_id, userKind.USL);
                
                results[6] = db.getNextVisits(patient_id, notMyDoctor_id, userKind.DOCTOR);
                results[7] = db.getNextVisits(patient_id, notMyDoctorButUsl_id, userKind.DOCTOR);
                
                results[8] = db.getNextVisits(false_patient_id, userKind.PATIENT);
                results[9] = db.getNextVisits(false_patient_id, doctor_id, userKind.DOCTOR);
                results[10] = db.getNextVisits(patient_id, false_doctor_id, userKind.DOCTOR);
                results[11] = db.getNextVisits(false_patient_id, false_doctor_id, userKind.DOCTOR);
            } catch (notMyPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getNotifiedVisitsByCell(patientCell);            
            
            /* the followings should return an error */
            results[1] = db.getNotifiedVisitsByCell(nobodysCell);            
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            Integer[] results = new Integer[TESTS];
            
            results[0] = db.getNumberOfDoneVisits(doctor_id);
            
            /* the followings should return an error */
            results[1] = db.getNumberOfDoneVisits(false_doctor_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 3;
            Integer[] results = new Integer[TESTS];
            
            results[0] = db.getNumberOfPatients();
            results[1] = db.getNumberOfPatients(doctor_id);
            
            /* the followings should return an error */
            results[2] = db.getNumberOfPatients(false_doctor_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            Integer[] results = new Integer[TESTS];
            
            results[0] = db.getNumberOfScheduledVisits(doctor_id);
            
            /* the followings should return an error */
            results[1] = db.getNumberOfScheduledVisits(false_doctor_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            Integer[] results = new Integer[TESTS];
            
            results[0] = db.getNumberOfVisits(doctor_id);
            
            /* the followings should return an error */
            results[1] = db.getNumberOfVisits(false_doctor_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 12;
            ResultSet[] results = new ResultSet[TESTS];
            
            try {
                results[0] = db.getPatientByID(patient_id, userKind.PATIENT);
                results[1] = db.getPatientByID(patient_id, doctor_id, userKind.DOCTOR);
                
                /* the followings should return an error */
                results[2] = db.getPatientByID(patient_id, userKind.DOCTOR);
                results[3] = db.getPatientByID(patient_id, userKind.USL);
                
                results[4] = db.getPatientByID(patient_id, userKind.PATIENT);
                
                results[5] = db.getPatientByID(patient_id, doctor_id, userKind.PATIENT);
                results[6] = db.getPatientByID(patient_id, doctor_id, userKind.USL);
                
                results[7] = db.getPatientByID(patient_id, false_doctor_id, userKind.DOCTOR);
                results[8] = db.getPatientByID(false_patient_id, doctor_id, userKind.DOCTOR);
                results[9] = db.getPatientByID(false_patient_id, false_doctor_id, userKind.DOCTOR);
                
                results[10] = db.getPatientByID(patient_id, notMyDoctor_id, userKind.DOCTOR);
                results[11] = db.getPatientByID(patient_id, notMyDoctorButUsl_id, userKind.DOCTOR);
            } catch (notMyPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }            
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 12;
            ResultSet[] results = new ResultSet[TESTS];
            
            try {
                results[0] = db.getPatientHistory(patient_id, userKind.PATIENT);
                results[1] = db.getPatientHistory(patient_id, doctor_id, userKind.DOCTOR);
                
                /* the followings should return an error */
                results[2] = db.getPatientHistory(patient_id, userKind.DOCTOR);
                results[3] = db.getPatientHistory(patient_id, userKind.USL);
                
                results[4] = db.getPatientHistory(patient_id, userKind.PATIENT);
                
                results[5] = db.getPatientHistory(patient_id, doctor_id, userKind.PATIENT);
                results[6] = db.getPatientHistory(patient_id, doctor_id, userKind.USL);
                
                results[7] = db.getPatientHistory(patient_id, false_doctor_id, userKind.DOCTOR);
                results[8] = db.getPatientHistory(false_patient_id, doctor_id, userKind.DOCTOR);
                results[9] = db.getPatientHistory(false_patient_id, false_doctor_id, userKind.DOCTOR);
                
                results[10] = db.getPatientHistory(patient_id, notMyDoctor_id, userKind.DOCTOR);
                results[11] = db.getPatientHistory(patient_id, notMyDoctorButUsl_id, userKind.DOCTOR);
            } catch (notMyPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }           
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 2;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getPatients(doctor_id);
            
            /* the followings should return an error */
            results[1] = db.getPatients(false_doctor_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 3;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.getSkippedVisits(userKind.USL);
            
            /* the followings should return an error */
            results[1] = db.getSkippedVisits(userKind.PATIENT);
            results[2] = db.getSkippedVisits(userKind.DOCTOR);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 8;
            String[] results = new String[TESTS];
            
            results[0] = db.getStrNameByID(patient_id, userKind.PATIENT);
            results[1] = db.getStrNameByID(doctor_id, userKind.DOCTOR);
            results[2] = db.getStrNameByID(usl_id, userKind.USL);
            
            /* the followings should return an error */
            results[3] = db.getStrNameByID(false_patient_id, userKind.PATIENT);
            results[4] = db.getStrNameByID(false_doctor_id, userKind.DOCTOR);
            results[5] = db.getStrNameByID(false_usl_id, userKind.USL);
            
            results[6] = db.getStrNameByID(doctor_id, userKind.USL);
            results[7] = db.getStrNameByID(usl_id, userKind.DOCTOR);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
            }
        }
        
        if (!test){
            int TESTS = 2;
            Boolean[] results = new Boolean[TESTS];
            
            results[0] = db.isNotified(record_id);
            
            /* the followings should return an error */
            results[1] = db.isNotified(false_record_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                out.println(results[i] + " ");
            }
        }
        
        if (!test){
            int TESTS = 14;
            ResultSet[] results = new ResultSet[TESTS];
            
            results[0] = db.matchingUser(patientUsername, patientPwd, userKind.PATIENT);
            results[1] = db.matchingUser(doctorUsername, doctorPwd, userKind.DOCTOR);
            results[2] = db.matchingUser(uslUsername, uslPwd, userKind.USL);
            
            /* the followings should return an error */
            results[3] = db.matchingUser(patientUsername, wrongPwd, userKind.PATIENT);
            results[4] = db.matchingUser(doctorUsername, wrongPwd, userKind.DOCTOR);
            results[5] = db.matchingUser(uslUsername, wrongPwd, userKind.USL);
            
            results[6] = db.matchingUser(doctorUsername, doctorPwd, userKind.USL);
            results[7] = db.matchingUser(uslUsername, doctorPwd, userKind.DOCTOR);
            
            results[8] = db.matchingUser(patientUsername, patientPwd, userKind.DOCTOR);
            results[9] = db.matchingUser(patientUsername, patientPwd, userKind.USL);
            
            results[10] = db.matchingUser(doctorUsername, doctorPwd, userKind.PATIENT);
            results[11] = db.matchingUser(doctorUsername, doctorPwd, userKind.USL);
            
            results[12] = db.matchingUser(uslUsername, uslPwd, userKind.PATIENT);
            results[13] = db.matchingUser(uslUsername, uslPwd, userKind.DOCTOR);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            out.println(db.now().format(DATE_FORMAT));
        }
        
        if (!test){
            int TESTS = 18;
            ResultSet[] results = new ResultSet[TESTS];
            
            try {
                String address = "address street 1", birthPlace = "birthPlace (here)",
                        cap = "33333", coords = "some coords", email = "email@mcr.it",
                        name = "nameW", surname = "surnameW", password = "qwerty",
                        phone = "19216811", picture = "myPicture.jpg";
                DateTime birthDate = db.now().minusDays(1000);
                
                PatientBean patientBean = new PatientBean();
                patientBean.setAddress(address);
                patientBean.setBirthdate(birthDate);
                patientBean.setBirthplace(birthPlace);                
                patientBean.setCap(cap);
                patientBean.setChiefTainId(chiefTain_id);
                patientBean.setCoords(coords);
                patientBean.setEmail(email);
                patientBean.setGenderEnum(EGender.M);
                patientBean.setName(name);
                patientBean.setParentId(patient_id);
                patientBean.setPassword(password);
                patientBean.setPhone(phone);
                patientBean.setPicture(picture);
                patientBean.setRefDoctorId(doctor_id);
                patientBean.setSurname(surname);
                patientBean.setUsername(null);
                
                db.registerPatient(patientBean);
                results[0] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                
                patientBean.setCoords(null);
                db.registerPatient(patientBean);
                results[10] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setCoords(coords);
                
                /* the followings should return an error */
                patientBean.setChiefTainId(notAChiefTain_id);
                db.registerPatient(patientBean);
                results[1] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setChiefTainId(chiefTain_id);
                
                patientBean.setChiefTainId(false_chiefTain_id);
                db.registerPatient(patientBean);
                results[2] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setChiefTainId(chiefTain_id);
                
                patientBean.setParentId(false_patient_id);
                db.registerPatient(patientBean);
                results[3] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setParentId(patient_id);
                
                patientBean.setRefDoctorId(usl_id);
                db.registerPatient(patientBean);
                results[4] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setRefDoctorId(doctor_id);
                
                patientBean.setRefDoctorId(false_doctor_id);
                db.registerPatient(patientBean);
                results[5] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setRefDoctorId(doctor_id);
                
                /* NOT NULL FIELDS */
                patientBean.setAddress(null);
                db.registerPatient(patientBean);
                results[6] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setAddress(address);
                
                patientBean.setBirthdate(null);
                db.registerPatient(patientBean);
                results[7] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setBirthdate(birthDate);
                
                patientBean.setBirthplace(null);                
                db.registerPatient(patientBean);
                results[8] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setBirthplace(birthPlace);                
                
                patientBean.setCap(null);
                db.registerPatient(patientBean);
                results[9] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setCap(cap);
                
                patientBean.setEmail(null);
                db.registerPatient(patientBean);
                results[11] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setEmail(email);
                
                patientBean.setGenderEnum(null);
                db.registerPatient(patientBean);
                results[12] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setGenderEnum(EGender.M);
                
                patientBean.setName(null);
                db.registerPatient(patientBean);
                results[13] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setName(name);
                               
                patientBean.setPassword(null);
                db.registerPatient(patientBean);
                results[14] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setPassword(password);
                
                patientBean.setPhone(null);
                db.registerPatient(patientBean);
                results[15] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setPhone(phone);
                
                patientBean.setPicture(null);
                db.registerPatient(patientBean);
                results[16] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setPicture(picture);
                                
                patientBean.setSurname(null);
                db.registerPatient(patientBean);    
                results[17] = db.getPatientByID(patientBean.getId(), userKind.PATIENT);
                patientBean.setSurname(surname);
                
            } catch (RegisterPatientException ex) {
                Log4k.warn(DBManager_tester.class.getName(), ex.getMessage());
            }
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 24;
            ResultSet[] results = new ResultSet[TESTS];
            
            /* patient before any change (then we will be progressive) */
            results[0] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            String address = "address street 1", birthPlace = "birthPlace (here)",
                    cap = "33333", coords = "some coords", email = "email@mcr.it",
                    name = "nameW", surname = "surnameW", password = "qwerty",
                    phone = "19216811", picture = "myPicture.jpg";
            DateTime birthDate = db.now().minusDays(1000);
            
            PatientBean patientBean = new PatientBean();
            patientBean.setId(patient_id);
            db.resetPatient(patientBean);
            results[1] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setAddress(address);
            db.resetPatient(patientBean);
            results[2] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setBirthdate(birthDate);
            db.resetPatient(patientBean);
            results[3] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            /* also set coords */
            patientBean.setBirthplace(birthPlace);
            db.resetPatient(patientBean);
            results[4] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setCap(cap);
            db.resetPatient(patientBean);
            results[5] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setChiefTainId(chiefTain_id);
            db.resetPatient(patientBean);
            results[6] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            /* do nothing */
            patientBean.setCoords(coords);
            db.resetPatient(patientBean);
            results[7] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setEmail(email);
            db.resetPatient(patientBean);
            results[8] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setGenderEnum(EGender.M);
            db.resetPatient(patientBean);
            results[9] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setName(name);
            db.resetPatient(patientBean);
            results[10] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setParentId(patient_id);
            db.resetPatient(patientBean);
            results[11] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setPassword(password);
            db.resetPatient(patientBean);
            results[12] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setPhone(phone);
            db.resetPatient(patientBean);
            results[13] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setPicture(picture);
            db.resetPatient(patientBean);
            results[14] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setRefDoctorId(doctor_id);
            db.resetPatient(patientBean);
            results[15] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setSurname(surname);
            db.resetPatient(patientBean);
            results[16] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            /* the followings should return an error */
            patientBean.setId(false_patient_id); /* try to reset a non-existing patient */
            db.resetPatient(patientBean);
            results[17] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setId(patient_id);
            
            patientBean.setUsername(wrongPwd);
            patientBean.setUsername(wrongPwd);
            db.resetPatient(patientBean); // should do nothing at all
            results[18] = db.getPatientByID(patient_id, userKind.PATIENT);
            
            patientBean.setChiefTainId(notAChiefTain_id);
            db.resetPatient(patientBean);
            results[19] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setChiefTainId(chiefTain_id);
            
            patientBean.setChiefTainId(false_chiefTain_id);
            db.resetPatient(patientBean);
            results[20] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setChiefTainId(chiefTain_id);
            
            patientBean.setRefDoctorId(false_doctor_id);
            db.resetPatient(patientBean);
            results[21] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setRefDoctorId(doctor_id);
            
            patientBean.setRefDoctorId(usl_id);
            db.resetPatient(patientBean);
            results[22] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setRefDoctorId(doctor_id);
            
            patientBean.setParentId(false_patient_id);
            db.resetPatient(patientBean);
            results[23] = db.getPatientByID(patient_id, userKind.PATIENT);
            patientBean.setParentId(patient_id);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            int TESTS = 3;
            ResultSet[] results = new ResultSet[TESTS];
            
            int lapse = 60*60*24*30;
            results[0] = db.retrieveVisitsToRemind(lapse, userKind.USL);
            
            /* the followings should return an error */
            results[1] = db.retrieveVisitsToRemind(lapse, userKind.PATIENT);
            results[2] = db.retrieveVisitsToRemind(lapse, userKind.DOCTOR);
            
            out.println("RESULTS:\t");
            for (int i=0; i<TESTS; i++){
                printSqlSet(results[i], out);
                out.println("\n");
            }
        }
        
        if (!test){
            db.sumAReminder(record_id);
            
            /* the followings should return an error */
            db.sumAReminder(false_record_id);
        }
        
        db.releaseConnection();        
    }
}
